package vivek.project.pocketmoney.stocks.model
{
	import flash.data.*;
	import flash.errors.*;
	import flash.events.Event;

	import mx.collections.ArrayCollection;
	import mx.collections.ArrayList;
	import mx.collections.IViewCursor;
	import mx.collections.ListCollectionView;
	import mx.collections.Sort;
	import mx.collections.SortField;
	import mx.controls.Alert;
	import mx.core.UIComponent;
	import mx.utils.*;

	import vivek.project.pocketmoney.admin.model.*;
	import vivek.project.pocketmoney.common.CommonData;
	import vivek.project.pocketmoney.common.utils.ArrayUtils;
	import vivek.project.pocketmoney.common.utils.CommonConstants;
	import vivek.project.pocketmoney.common.utils.DateUtils;
	import vivek.project.pocketmoney.expenses.model.ExpenseDataModel;
	import vivek.project.pocketmoney.stocks.StocksEvents;
	import vivek.project.pocketmoney.stocks.scripts.*;

	public class StockDataModel extends UIComponent
	{
		//private  var _sqlConnection : SQLConnection =CommonData.getConnection();
		private static var instance:StockDataModel;

		public static function getInstance():StockDataModel
		{
			if (!instance)
			{
				/*instance = ExpenseDataModel(
				 Singleton.getInstance("vivek.project.pocketmoney.expenses.model::ExpenseDataModel"));*/
				instance=new StockDataModel();
			}
			return instance;
		}

		public static function getStockNameForId(id:int):String
		{
			for each (var obj:Object in StockDataCache.stockList)
			{
				if (obj.sc_id == id)
					return obj.sc_stockname;
			}
			return "";
		}

		public static function getStockIdForName(name:String):int
		{
			for each (var obj:Object in StockDataCache.stockList)
			{
				if (obj.sc_stockname.toString().indexOf(name.toUpperCase()) != -1)
					return obj.sc_id;
			}
			return 0;
		}

		public function StockDataModel()
		{
			loadData();
			AdminDataModel.getInstance().addEventListener("USERCHANGED", loadDataOnEvent);
			addEventListener(StocksEvents.StockTransactionAdded, refreshTransactionRelatedData)
			addEventListener(StocksEvents.StockTransactionUpdated, refreshTransactionRelatedData)
			addEventListener(StocksEvents.StockTransactionDeleted, refreshTransactionRelatedData)
			addEventListener(StocksEvents.StockPriceUpdated, refreshPriceRelatedData)
			addEventListener(StocksEvents.StockWatchlistChanged, refreshWatchList)
			addEventListener(StocksEvents.StockListUpdated, refreshStockList)
		}
		private var edm:ExpenseDataModel=ExpenseDataModel.getInstance();

		public function addStockControl(stockObj:Object):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				var _sqlConnection:SQLConnection=CommonData.getConnection();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=StockSqlScripts.ADD_NEW_STOCKCONTROL;
				_statement.parameters[":sc_name"]=stockObj.name;
				_statement.parameters[":sc_description"]=stockObj.description;
				_statement.parameters[":sc_type"]=stockObj.type;
				_statement.execute();
				refreshStockList(null);
				refreshIndices();
			}
			catch (sqlError:SQLError)
			{
				trace(sqlError.toString());
					//Alert.show(sqlError.details);
			}
			return 1;
		}

		public function addStockPriceToDatabase(object:Object):void
		{
			var _statement:SQLStatement=new SQLStatement();
			var _sqlConnection:SQLConnection=CommonData.getConnection();
			//_sqlConnection.open();
			_statement.sqlConnection=_sqlConnection;
			try
			{
				_sqlConnection.begin();
				_statement.text=StockSqlScripts.DELETE_STOCK_PRICE;
				_statement.parameters[":sp_id"]=object.stockId;
				_statement.execute();
				_statement.text=StockSqlScripts.INSERT_STOCK_PRICE;
				var obj:Object=new Object();
				obj.stockId=object.stockId;
				obj.bseStockPrice=object.bseStockPrice;
				obj.bsePreviousClose=object.bsePreviousClose;
				obj.nseStockPrice=object.nseStockPrice;
				obj.nsePreviousClose=object.nsePreviousClose;
				StockDataCache.stockHistory.addItem(obj);
				_statement.parameters[":sp_id"]=object.stockId;
				_statement.parameters[":sp_bseprice"]=new Number(object.bseStockPrice);
				_statement.parameters[":sp_bsepreviousclose"]=new Number(object.bsePreviousClose);
				_statement.parameters[":sp_nseprice"]=new Number(object.nseStockPrice);
				_statement.parameters[":sp_nsepreviousclose"]=new Number(object.nsePreviousClose);
				//_statement.parameters[":sp_bselasttradedtime"] = nsePreviousClose;
				_statement.execute();
				_sqlConnection.commit();
				dispatchEvent(new Event(StocksEvents.StockPriceUpdated));
			}
			catch (sqlError:SQLError)
			{
				_sqlConnection.rollback();
				_sqlConnection.close();
				trace(object.stockId + sqlError.message);
			}
			_sqlConnection.close();
			trace(object.stockId, object.bseStockPrice, object.nseStockPrice, object.nsePreviousClose, object.bsePreviousClose);
			//StockDataModel.getInstance().refreshPrices();
		}

		public function updateStockQtyField(qty:Number, price:Number, brokerage:Number, mfId:Number):void
		{
			var sqlConnection:SQLConnection=CommonData.getConnection();
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=sqlConnection;
				var UPDATE_STOCK_RECORD_FIELD:String="UPDATE stocktransaction SET st_quantity=:quantity , st_price =:price, st_brokerage =:brokerage WHERE st_id=:mfId";
				_statement.text=UPDATE_STOCK_RECORD_FIELD;
				//_statement.parameters[":FIELDNAME"]=fieldName;
				_statement.parameters[":quantity"]=qty;
				_statement.parameters[":price"]=price;
				_statement.parameters[":brokerage"]=brokerage;
				_statement.parameters[":mfId"]=mfId;
				_statement.execute();
				//refreshExpenseData(sqlConnection);
				sqlConnection.close();
				_statement=null;
				edm.updateExpenseField("em_amount", (qty * price) + brokerage, mfId);
				//loadData(); //TODO:				
				dispatchEvent(new Event(StocksEvents.StockTransactionUpdated));
			}
			catch (e:Error)
			{
				trace(e.message);
			}
		}

		public function updateStockField(fieldName:String, value:Object, stockId:Number):void
		{
			var sqlConnection:SQLConnection=CommonData.getConnection();
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				_statement.sqlConnection=sqlConnection;
				var UPDATE_STOCK_RECORD_FIELD:String="UPDATE stocktransaction SET " + fieldName + " = :VALUE WHERE st_id=:stockId";
				_statement.text=UPDATE_STOCK_RECORD_FIELD;
				//_statement.parameters[":FIELDNAME"]=fieldName;
				_statement.parameters[":VALUE"]=value;
				_statement.parameters[":stockId"]=stockId;
				_statement.execute();
				//refreshExpenseData(sqlConnection);
				sqlConnection.close();
				_statement=null;
				//loadData(); //TODO:
				//edm.refreshExpenseDataCache(null);
				//PMLogger.log("EXPENSE", "EXPENSE UPDATED :" + expenseId + " , field " + fieldName + "," + value);
				dispatchEvent(new Event(StocksEvents.StockTransactionUpdated));
			}
			catch (e:Error)
			{
				trace(e.message);
			}
		}

		public function addStockToWatchList(stockObj:Array):int
		{
			var _statement:SQLStatement=new SQLStatement();
			var _sqlConnection:SQLConnection=CommonData.getConnection();
			_statement.sqlConnection=_sqlConnection;
			_statement.text=StockSqlScripts.ADD_STOCK_TO_WATCHLIST;
			var obj:Object=new Object();
			for each (obj in stockObj)
			{
				_statement.parameters[":sc_id"]=obj.sc_id;
				_statement.execute();
			}
			//TODO:
			//refreshWatchList();
			dispatchEvent(new Event(StocksEvents.StockWatchlistChanged));
			return 0;
		}

		public function addStockTransaction(stockObj:Object):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				var randomId:Number=new Number();
				randomId=Math.round(Math.random() * 1000000000);
				var _sqlConnection:SQLConnection=CommonData.getConnection();
				_statement.sqlConnection=_sqlConnection;
				_sqlConnection.begin();
				_statement.text=StockSqlScripts.ADD_NEW_STOCKTRANSACTION;
				_statement.parameters[":st_stockid"]=stockObj.stockid;
				_statement.parameters[":st_date"]=stockObj.txndate;
				_statement.parameters[":st_quantity"]=stockObj.qty;
				_statement.parameters[":st_price"]=stockObj.price;
				_statement.parameters[":st_type"]=stockObj.type;
				_statement.parameters[":st_exchange"]=stockObj.exchange;
				_statement.parameters[":st_channelid"]=stockObj.channel;
				_statement.parameters[":st_id"]=randomId;
				_statement.parameters[":st_brokerage"]=stockObj.brokerage;
				_statement.parameters[":st_accountid"]=stockObj.account;
				_statement.parameters[":USERID"]=stockObj.userid;
				_statement.execute();
				if (stockObj.isAccountEntryRequired)
				{
					var expRecord:Object=new Object();
					if (stockObj.type == CommonConstants.BUY)
					{
						expRecord.account=stockObj.account;
						expRecord.type=AdminConstants.WITHDRAWAL;
						expRecord.memo="Bought Stock : " + getStockNameForId(stockObj.stockid);
						//expRecord.transferAccount=stockObj.channel;
						expRecord.amount=(new Number(stockObj.price) * new Number(stockObj.qty)) + new Number(stockObj.brokerage);
					}
					else
					{
						expRecord.account=stockObj.account;
						expRecord.type=AdminConstants.DEPOSIT;
						expRecord.memo="Sold Stock : " + getStockNameForId(stockObj.stockid);
						//expRecord.account=stockObj.channel;
						//expRecord.transferAccount=stockObj.account;
						expRecord.amount=(new Number(stockObj.price) * new Number(stockObj.qty)) - new Number(stockObj.brokerage);
					}
					expRecord.category=CommonConstants.STOCK_CATEGORY_ID;
					expRecord.payee=0;
					expRecord.date=stockObj.txndate;
					expRecord.exRate=1.0;
					expRecord.userid=stockObj.userid;
					edm.addDependentExpenseRecord(expRecord, randomId, _sqlConnection);
				}
				_sqlConnection.commit();
				//TODO:
				//loadData();
				edm.refreshExpenseDataCache(null);
				dispatchEvent(new Event(StocksEvents.StockTransactionAdded));
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
			}
			return 1;
		}

		public function addStockTransactions(stockObjs:Array):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				var _sqlConnection:SQLConnection=CommonData.getConnection();
				_statement.text=StockSqlScripts.ADD_NEW_STOCKTRANSACTION;
				_statement.sqlConnection=_sqlConnection;
				_sqlConnection.begin();

				for each (var stockObj:Object in stockObjs)
				{

					var randomId:Number=new Number();
					randomId=Math.round(Math.random() * 1000000000);
					_statement.parameters[":st_stockid"]=stockObj.stockid;
					_statement.parameters[":st_date"]=stockObj.txndate;
					_statement.parameters[":st_quantity"]=stockObj.qty;
					_statement.parameters[":st_price"]=stockObj.price;
					_statement.parameters[":st_type"]=stockObj.type;
					_statement.parameters[":st_exchange"]=stockObj.exchange;
					_statement.parameters[":st_channelid"]=stockObj.channel;
					_statement.parameters[":st_id"]=randomId;
					_statement.parameters[":st_brokerage"]=stockObj.brokerage;
					_statement.parameters[":st_accountid"]=stockObj.account;
					_statement.parameters[":USERID"]=stockObj.userid;
					_statement.execute();
					if (stockObj.isAccountEntryRequired)
					{
						var expRecord:Object=new Object();
						if (stockObj.type == CommonConstants.BUY)
						{
							expRecord.account=stockObj.account;
							expRecord.type=AdminConstants.WITHDRAWAL;
							expRecord.memo="Bought Stock : " + getStockNameForId(stockObj.stockid);
							//expRecord.transferAccount=stockObj.channel;
							expRecord.amount=(new Number(stockObj.price) * new Number(stockObj.qty)) + new Number(stockObj.brokerage);
						}
						else
						{
							expRecord.account=stockObj.account;
							expRecord.type=AdminConstants.DEPOSIT;
							expRecord.memo="Sold Stock : " + getStockNameForId(stockObj.stockid);
							//expRecord.account=stockObj.channel;
							//expRecord.transferAccount=stockObj.account;
							expRecord.amount=(new Number(stockObj.price) * new Number(stockObj.qty)) - new Number(stockObj.brokerage);
						}
						expRecord.category=CommonConstants.STOCK_CATEGORY_ID;
						expRecord.payee=0;
						expRecord.date=stockObj.txndate;
						expRecord.exRate=1.0;
						expRecord.userid=stockObj.userid;
						edm.addDependentExpenseRecord(expRecord, randomId, _sqlConnection);
					}

				}
				_sqlConnection.commit();
				//TODO:
				//loadData();
				edm.refreshExpenseDataCache(null);
				dispatchEvent(new Event(StocksEvents.StockTransactionAdded));
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
			}
			return 1;
		}

		public function deleteStock(stockId:int):int
		{
			var _statement:SQLStatement=new SQLStatement();
			try
			{
				var _sqlConnection:SQLConnection=CommonData.getConnection();
				_statement.sqlConnection=_sqlConnection;
				_sqlConnection.begin();
				_statement.text=StockSqlScripts.DELETE_STOCK_CONTROL;
				_statement.parameters[":sc_id"]=stockId;
				_statement.execute();
				_sqlConnection.commit();
			}
			catch (sqlError:SQLError)
			{
				//Alert.show("Error :" + sqlError.details);
				_sqlConnection.rollback();
				return 1;
			}
			//TODO:
			//refreshStockList();
			dispatchEvent(new Event(StocksEvents.StockListUpdated));
			return 0;
		}

		public function deleteStockTransaction(stockObj:Object):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				var _sqlConnection:SQLConnection=CommonData.getConnection();
				_statement.sqlConnection=_sqlConnection;
				_sqlConnection.begin();
				_statement.text=StockSqlScripts.DELETE_STOCK_TRANSACTION;
				_statement.parameters[":st_id"]=stockObj.stockid;
				_statement.execute();
				edm.deleteDependentExpense(stockObj.stockid, true, _sqlConnection);
				_sqlConnection.commit();
				//TODO:
				StockDataCache.stockTransactions.source=getSortedTransactions();
				edm.refreshExpenseDataCache(null);
				dispatchEvent(new Event(StocksEvents.StockTransactionDeleted));
			}
			catch (sqlError:SQLError)
			{
				//Alert.show("Error :" + sqlError.details);
				_sqlConnection.rollback();
				return 1;
			}
			return 0;
		}

		public function generateNSETickerData():String
		{
			var str:String="";
			var stylename:String="red";
			for each (var obj:Object in StockDataCache.stockPrices)
			{
				if (obj.sc_watchlist == 1)
					if (obj.sp_nseprice > obj.sp_nsepreviousclose)
						str=str + "<font  size=1>" + obj.sc_stockname + "</font>" + " <font color='green' size=1>" + obj.sp_nseprice + "&nbsp;</font>";
					else
						str=str + "<font size=1>" + obj.sc_stockname + "</font>" + " <font color='red' size=1>" + obj.sp_nseprice + "&nbsp;</font>";
			}
			var commonstring:String="<MARQUEE WIDTH=100% BEHAVIOR=SCROLL>" + str + "</MARQUEE>";
			return commonstring;
		}

		public function generateBSETickerData():String
		{
			var str:String="";
			for each (var obj:Object in StockDataCache.stockPrices)
			{
				if (obj.sc_watchlist == 1)
					if (obj.sp_bseprice > obj.sp_bsepreviousclose)
						str=str + "<font  size=1>" + obj.sc_stockname + "</font>" + " <font color='green' size=1>" + obj.sp_bseprice + "&nbsp;</font>";
					else
						str=str + "<font  size=1>" + obj.sc_stockname + "</font>" + " <font color='green' size=1>" + obj.sp_bseprice + "&nbsp;</font>";
			}
			var commonstring:String="<MARQUEE WIDTH=100% BEHAVIOR=SCROLL>" + str + "</MARQUEE>";
			return commonstring;
		}

		public function deleteStockTransactions(stocks:Array):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				var _sqlConnection:SQLConnection=CommonData.getConnection();
				_statement.sqlConnection=_sqlConnection;
				_sqlConnection.begin();
				_statement.text=StockSqlScripts.DELETE_STOCK_TRANSACTION;
				for each (var stockObj:Object in stocks)
				{
					_statement.parameters[":st_id"]=stockObj.st_id;
					_statement.execute();
					edm.deleteDependentExpense(stockObj.st_id, true, _sqlConnection);
				}
				_sqlConnection.commit();
				//loadData();
				edm.refreshExpenseDataCache(null);
				dispatchEvent(new Event(StocksEvents.StockTransactionDeleted));
			}
			catch (sqlError:SQLError)
			{
				//Alert.show("Error :" + sqlError.details);
				_sqlConnection.rollback();
				return 1;
			}
			return 0;
		}

		public function getStockCurrentPrice(stockId:int):int
		{
			var obj:Object=new Object();
			for each (obj in StockDataCache.stockPrices)
			{
				if (obj.sp_id == stockId)
					return obj.sp_bseprice;
			}
			return 0;
		}

		public function getStockProfitLoss():Array
		{
			var allTxn:Array=new Array();
			var plArray:Array=new Array();
			allTxn=getSortedTransactions();
			var finalObj:Array=new Array();
			var txnObj:Object=new Object();
			if (allTxn == null)
				return null;
			if (allTxn.length == 0)
				return null;
			var tempObj:Object=new Object();
			tempObj=ObjectUtil.copy(allTxn[0]);
			tempObj.qty=0.00;
			tempObj.sellValue=0;
			tempObj.sellDate=0;
			tempObj.sellQty=0;
			StockDataCache.currentHoldingValue=0;
			tempObj.buyValue=0.00;
			for each (txnObj in allTxn)
			{
				if (tempObj.st_stockid == txnObj.st_stockid)
				{
					if (txnObj.st_type == CommonConstants.BUY)
					{
						tempObj.buyValue=tempObj.buyValue + ((txnObj.st_quantity * txnObj.st_price) + txnObj.st_brokerage);
						tempObj.qty=tempObj.qty + txnObj.st_quantity;
						tempObj.sellDate=tempObj.st_date;
					}
					else
					{
						tempObj.sellValue=tempObj.sellValue + ((txnObj.st_quantity * txnObj.st_price) - txnObj.st_brokerage);
						tempObj.sellQty=tempObj.sellQty + txnObj.st_quantity;
						tempObj.sellDate=txnObj.st_date;
					}
					tempObj.inHand=tempObj.qty - tempObj.sellQty;
				}
				else
				{
					tempObj.currentValue=tempObj.inHand * getStockCurrentPrice(tempObj.st_stockid);
					finalObj.push(tempObj);
					StockDataCache.currentHoldingValue+=tempObj.currentValue;
					tempObj=ObjectUtil.copy(txnObj);
					tempObj.qty=0.00;
					tempObj.sellValue=0;
					tempObj.sellQty=0;
					tempObj.buyValue=0.00;
					if (txnObj.st_type == CommonConstants.BUY)
					{
						tempObj.buyValue=tempObj.buyValue + ((txnObj.st_quantity * txnObj.st_price) + txnObj.st_brokerage);
						tempObj.qty=tempObj.qty + txnObj.st_quantity;
					}
					else
					{
						tempObj.sellValue=tempObj.sellValue + ((txnObj.st_quantity * txnObj.st_price) - txnObj.st_brokerage);
						tempObj.sellQty=tempObj.sellQty + txnObj.st_quantity;
						tempObj.sellDate=txnObj.st_date;
					}
					tempObj.inHand=tempObj.qty - tempObj.sellQty;
				}
			}
			tempObj.currentValue=tempObj.inHand * getStockCurrentPrice(tempObj.st_stockid);
			finalObj.push(tempObj);
			StockDataCache.currentHoldingValue+=tempObj.currentValue;
			return finalObj;
		}

		public function getStockProfitLossByChannel():Array
		{
			var allTxn:Array=new Array();
			var plArray:Array=new Array();
			allTxn=getSortedTransactions();
			var finalObj:Array=new Array();
			//finalObj.sort = new Sort();
			//finalObj.sort.fields=[new SortField("st_channelid",false)] ;
			var txnObj:Object=new Object();
			if (allTxn == null)
				return null;
			if (allTxn.length == 0)
				return null;
			var tempObj:Object=new Object();
			tempObj=ObjectUtil.copy(allTxn[0]);
			tempObj.qty=0.00;
			tempObj.sellValue=0;
			tempObj.sellQty=0;
			tempObj.buyValue=0.00;
			var matchingFields:Array=new Array();
			matchingFields.push("st_channelid");
			matchingFields.push("st_stockid");
			for each (txnObj in allTxn)
			{
				var i:Array=ArrayUtils.findMatchingItem(txnObj, finalObj, matchingFields);
				//if (tempObj.st_stockid == txnObj.st_stockid && tempObj.st_channelid == txnObj.st_channelid)
				if (i.length != 0)
				{
					tempObj=new Object();
					tempObj=i[0];
					if (txnObj.st_type == CommonConstants.BUY)
					{
						tempObj.buyValue=tempObj.buyValue + ((txnObj.st_quantity * txnObj.st_price) + txnObj.st_brokerage);
						tempObj.qty=tempObj.qty + txnObj.st_quantity;
					}
					else
					{
						tempObj.sellValue=tempObj.sellValue + ((txnObj.st_quantity * txnObj.st_price) - txnObj.st_brokerage);
						tempObj.sellQty=tempObj.sellQty + txnObj.st_quantity;
						tempObj.sellDate=txnObj.st_date;
					}
				}
				else
				{
					tempObj=ObjectUtil.copy(txnObj);
					tempObj.qty=0.00;
					tempObj.sellValue=0;
					tempObj.sellQty=0;
					tempObj.buyValue=0.00;
					if (txnObj.st_type == CommonConstants.BUY)
					{
						tempObj.buyValue=tempObj.buyValue + ((txnObj.st_quantity * txnObj.st_price) + txnObj.st_brokerage);
						tempObj.qty=tempObj.qty + txnObj.st_quantity;
					}
					else
					{
						tempObj.sellValue=tempObj.sellValue + ((txnObj.st_quantity * txnObj.st_price) - txnObj.st_brokerage);
						tempObj.sellQty=tempObj.sellQty + txnObj.st_quantity;
						tempObj.sellDate=txnObj.st_date;
					}
					finalObj.push(tempObj);
				}
			}
			return finalObj;
		}

		public function refreshPrices():void
		{
			var obj:Object=new Object();
			var _statement:SQLStatement=new SQLStatement();
			//var stockPrices:ArrayCollection=new ArrayCollection();
			var _sqlConnection:SQLConnection=CommonData.getConnection();
			_statement.sqlConnection=_sqlConnection;
			_statement.text=StockSqlScripts.SELECT_STOCK_PRICES;
			_statement.execute();
			_sqlConnection.close();
			StockDataCache.stockPrices.source=null;
			StockDataCache.stockPrices.source=_statement.getResult().data;
		}

		public function removeStockFromWatchList(stockObj:Array):int
		{
			var _statement:SQLStatement=new SQLStatement();
			var _sqlConnection:SQLConnection=CommonData.getConnection();
			_statement.sqlConnection=_sqlConnection;
			_statement.text=StockSqlScripts.REMOVE_STOCK_FROM_WATCHLIST;
			var obj:Object=new Object();
			for each (obj in stockObj)
			{
				_statement.parameters[":sc_id"]=obj.sc_id;
				_statement.execute();
			}
			//TODO:
			refreshWatchList();
			dispatchEvent(new Event(StocksEvents.StockWatchlistChanged));
			return 0;
		}

		public function updateStockControl(stockObj:Object):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				var _sqlConnection:SQLConnection=CommonData.getConnection();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=StockSqlScripts.UPDATE_NEW_STOCKCONTROL;
				_statement.parameters[":sc_name"]=stockObj.name;
				_statement.parameters[":sc_description"]=stockObj.description;
				_statement.parameters[":sc_type"]=stockObj.type;
				_statement.parameters[":sc_id"]=stockObj.id;
				_statement.execute();
				//TODO:
				dispatchEvent(new Event(StocksEvents.StockListUpdated));
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
			}
			return 1;
		}

		public function updateStockTransaction(stockObj:Object):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				var _sqlConnection:SQLConnection=CommonData.getConnection();
				_statement.sqlConnection=_sqlConnection;
				_sqlConnection.begin();
				_statement.text=StockSqlScripts.UPDATE_STOCKTRANSACTION;
				_statement.parameters[":st_stockid"]=stockObj.stockid;
				_statement.parameters[":st_date"]=stockObj.txndate;
				_statement.parameters[":st_quantity"]=stockObj.qty;
				_statement.parameters[":st_price"]=stockObj.price;
				_statement.parameters[":st_type"]=stockObj.type;
				_statement.parameters[":st_exchange"]=stockObj.exchange;
				_statement.parameters[":st_channelid"]=stockObj.channel;
				_statement.parameters[":st_brokerage"]=stockObj.brokerage;
				_statement.parameters[":st_accountid"]=stockObj.account;
				_statement.parameters[":transactionid"]=stockObj.transactionId;
				_statement.execute();
				var expRecord:Object=new Object();
				if (stockObj.type == CommonConstants.BUY)
				{
					expRecord.account=stockObj.account;
					expRecord.type=AdminConstants.WITHDRAWAL;
					//expRecord.account=stockObj.account;
					//expRecord.transferAccount=stockObj.channel;
					expRecord.amount=(new Number(stockObj.price) * new Number(stockObj.qty)) + new Number(stockObj.brokerage);
				}
				else
				{
					expRecord.account=stockObj.account;
					expRecord.type=AdminConstants.DEPOSIT;
					//expRecord.account=stockObj.channel;
					//expRecord.transferAccount=stockObj.account;
					expRecord.amount=(new Number(stockObj.price) * new Number(stockObj.qty)) - new Number(stockObj.brokerage);
				}
				expRecord.category=CommonConstants.STOCK_CATEGORY_ID;
				expRecord.payee=0;
				expRecord.memo="Stock";
				expRecord.date=stockObj.txndate;
				expRecord.exRate=1.0;
				edm.updateDependentExpense(expRecord, stockObj.transactionId, _sqlConnection);
				_sqlConnection.commit();
				//TODO:
				//refreshTransactionRelatedData();
				edm.refreshExpenseDataCache(null);
				dispatchEvent(new Event(StocksEvents.StockTransactionUpdated));
			}
			catch (sqlError:SQLError)
			{
				trace(sqlError.details);
					//Alert.show(sqlError.details);
			}
			return 1;
		}

		private function getSortedTransactions():Array
		{
			var _statement:SQLStatement=new SQLStatement();
			var _sqlConnection:SQLConnection=CommonData.getConnection();
			_statement.sqlConnection=_sqlConnection;
			_statement.text=StockSqlScripts.SELECT_STOCK_TRANSACTIONS_SORTED;
			//_statement.parameters[":USERID"]=AdminDataCache.ACCOUNT_IN_USE;
			_statement.execute();
			return _statement.getResult().data;
		}

		private function loadData():void
		{
			refreshStockList(null);
			refreshPrices();
			StockDataCache.stockTransactions.source=getSortedTransactions();
			refreshWatchList();
			calculateBuySell();
			refreshIndices();
			StockDataCache.profiltLoss.source=getStockProfitLoss();
			StockDataCache.profiltLossByChannel.source=(getStockProfitLossByChannel());
			dispatchEvent(new Event(StocksEvents.StockDataCacheUpdated));
		}

		private function refreshPriceRelatedData(event:Event):void
		{
			refreshPrices();
			StockDataCache.profiltLoss.source=getStockProfitLoss();
			StockDataCache.profiltLossByChannel.source=(getStockProfitLossByChannel());
			dispatchEvent(new Event(StocksEvents.StockDataCacheUpdated));
		}

		private function refreshTransactionRelatedData(event:Event):void
		{
			StockDataCache.stockTransactions.source=getSortedTransactions();
			StockDataCache.profiltLoss.source=getStockProfitLoss();
			StockDataCache.profiltLossByChannel.source=(getStockProfitLossByChannel());
			calculateBuySell();
			dispatchEvent(new Event(StocksEvents.StockDataCacheUpdated));
		}

		private function refreshStockList(event:Event):void
		{
			var _statement:SQLStatement=new SQLStatement();
			var _sqlConnection:SQLConnection=CommonData.getConnection();
			_statement.sqlConnection=_sqlConnection;
			_statement.text=StockSqlScripts.SELECT_STOCK_LIST;
			_statement.execute();
			StockDataCache.stockList.source=_statement.getResult().data;
			_sqlConnection.close();
			_statement=null;
			//dispatchEvent(new Event(StocksEvents.StockListUpdated));
		}

		private function refreshStockPrices():void
		{
			var _statement:SQLStatement=new SQLStatement();
			var _sqlConnection:SQLConnection=CommonData.getConnection();
			_statement.sqlConnection=_sqlConnection;
			_statement.text=StockSqlScripts.SELECT_STOCK_PRICES;
			_statement.execute();
			StockDataCache.stockPrices.source=_statement.getResult().data;
			_sqlConnection.close();
			_statement=null;
			dispatchEvent(new Event(StocksEvents.StockPriceUpdated));
		}

		private function loadDataOnEvent(event:Event):void
		{
			loadData();
		}

		private function refreshIndices():Array
		{
			var obj:Object=new Object();
			var stockIndices:Array=new Array();
			for each (obj in StockDataCache.stockList)
			{
				if (obj.sc_stocktype == 1)
					stockIndices.push(obj);
			}
			StockDataCache.stockIndices.source=stockIndices


			return stockIndices;
		}

		private function refreshWatchList():Array
		{
			var obj:Object=new Object();
			var stockWatchList:Array=new Array();
			for each (obj in StockDataCache.stockList)
			{
				if (obj.sc_watchlist == 1)
					stockWatchList.push(obj);
			}
			StockDataCache.stockWatchList.source=stockWatchList
			return stockWatchList;
		}

		private function calculateBuySell():void
		{
			trace("stock : calculate buy sell  :\t " + new Date().toTimeString() + " \t:  Started");
			var buyArray:Array=new Array();
			var finalArray:Array=new Array();
			var sellArray:ArrayCollection=new ArrayCollection();
			var mfList:ListCollectionView=new ListCollectionView(StockDataCache.stockTransactions);
			sellArray.disableAutoUpdate();
			//MutualFundDataCache.currentHoldingValue=0;
			//	buyArray.disableAutoUpdate();						
			var sort:Sort=new Sort();
			sort.fields=[new SortField("st_channel", false), new SortField("st_stockid", false), new SortField("smt_date", false)];
			mfList.sort=sort;
			mfList.refresh();
			var object:Object=new Object();
			var tempObject:Object=new Object();
			for each (object in mfList)
			{
				tempObject=new Object();
				tempObject.st_available=object.st_quantity;
				tempObject.st_channel=object.st_channelid;
				tempObject.st_stockid=object.st_stockid;
				tempObject.sc_description=object.sc_description;
				tempObject.st_amount=object.st_quantity * object.st_price;
				tempObject.st_quantity=object.st_quantity;

				tempObject.st_date=object.st_date;

				tempObject.st_price=object.st_price;
				tempObject.sellunit=0;
				tempObject.sellnav=0;
				tempObject.selldate=0;
				tempObject.sellamount=0;
				tempObject.st_daysSell=DateUtils.dateDiff(new Date(), tempObject.st_date);
				//tempObject.mt_currentvalue=Math.round(tempObject.mt_available * StockDataCache.hash.find(tempObject.st_stockid) * 100) / 100;
				tempObject.st_profit=tempObject.st_currentvalue - tempObject.st_amount;
				//tempObject.st_cagrReturn=0; //FinancialFormulas.calculateCAGR(tempObject.st_amount, tempObject.st_currentvalue, tempObject.sellamount, tempObject.st_daysSell / 365);
				//tempObject.st_absReturn=0; //ancialFormulas.calculateAbsReturns(tempObject.st_amount, tempObject.st_currentvalue, tempObject.sellamount, tempObject.st_daysSell / 12);
				//tempObject.st_anReturn=0; //FinancialFormulas.calculateAnnualizedReturn(tempObject.st_amount, tempObject.st_currentvalue, tempObject.sellamount, tempObject.st_daysSell);
				//				tempObject.st_dString=object.st_date.toDateString();
				if (object.st_type == StockConstants.BUY)
				{
					buyArray.push(tempObject);
				}
				else
				{
					sellArray.addItem(tempObject);
				}
			}
			var buySort:Sort=new Sort();
			buySort.fields=[new SortField("st_channelid", false), new SortField("st_stockid", false)];
			//buyArray.sort=buySort;
			//buyArray.refresh();
			var sellSort:Sort=new Sort();
			sellSort.fields=[new SortField("st_channelid", false), new SortField("st_stockid", false)];
			//sellArray.sort=sellSort;
			//sellArray.refresh();
			//var buyCursor:IViewCursor=sellArray.createCursor();
			var sellCursor:IViewCursor=sellArray.createCursor();
			var sellObject:Object=new Object();
			var buyObject:Object=new Object();
			var matchArray:Array=new Array();
			while (!sellCursor.afterLast)
			{
				sellObject=sellCursor.current;
				//	if (sellObject.st_stockid==695)
				{
					matchArray=ArrayUtils.findMatchingItem(sellObject, buyArray, ['st_channelid', 'st_stockid']);
					if (matchArray.length != 0)
					{
						buyObject=matchArray[0];
						if (buyObject.st_available != 0)
						{
							if (buyObject.st_available == sellObject.st_available)
							{

								buyObject.sellunit=sellObject.st_available;
								buyObject.sellnav=sellObject.st_price;
								buyObject.selldate=sellObject.st_date;
								buyObject.st_available=0;
								buyObject.sellamount=(buyObject.sellnav * buyObject.sellunit);
								buyObject.st_profit=buyObject.sellamount - buyObject.st_amount;
								sellObject.st_available=0;
								finalArray.push(buyObject);
								buyObject.st_currentvalue=0;
								//buyCursor.remove();
								buyArray.splice(buyArray.indexOf(buyObject), 1);
							}
							else
							{
								if (sellObject.st_available > buyObject.st_available)
								{
									buyObject.sellunit=buyObject.st_available;
									buyObject.sellnav=sellObject.st_price;
									buyObject.selldate=sellObject.st_date;
									sellObject.st_available=sellObject.st_available - buyObject.st_available;
									buyObject.st_available=0;
									buyObject.sellamount=(buyObject.sellnav * buyObject.sellunit);
									buyObject.st_profit=buyObject.sellamount - buyObject.st_amount;
									finalArray.push(buyObject);
									buyObject.st_currentvalue=0;
									var i:int=buyArray.indexOf(buyObject);
									buyArray.splice(i, 1);
									sellCursor.movePrevious();
										//trace(buyObject.st_stockid + "\t"+buyObject.st_quantity + "\t"+buyObject.st_available+"\t"+buyObject.st_profit);
								}
								else
								{
									var partSellObject:Object=new Object();
									partSellObject=ObjectUtil.copy(buyObject);
									partSellObject.sellunit=sellObject.st_available;
									partSellObject.sellnav=sellObject.st_price;
									partSellObject.selldate=sellObject.st_date;
									buyObject.st_available=buyObject.st_available - sellObject.st_available;
									buyObject.st_amount=buyObject.st_available * buyObject.st_price;
									buyObject.st_quantity=buyObject.st_available;
									partSellObject.sellamount=partSellObject.sellnav * partSellObject.sellunit;
									partSellObject.st_profit=partSellObject.sellamount - (partSellObject.sellunit * partSellObject.st_price);
									partSellObject.st_currentvalue=0;
									partSellObject.st_amount=partSellObject.sellunit * partSellObject.st_price;
									sellObject.st_available=0;
									buyObject.st_profit=buyObject.st_currentvalue - buyObject.st_amount;
									partSellObject.st_daysSell=DateUtils.dateDiff(partSellObject.st_date, partSellObject.st_date);
									finalArray.push(partSellObject);
										//trace(partSellObject.st_stockid + "\tsell unit"+partSellObject.st_quantity + "\tavailable"+partSellObject.st_available+ "\tProfit"+partSellObject.st_profit+"\tPartSell");
								}
							}
						}
					}
					else
					{
						//	buyCursor.
					}
				}
				sellCursor.moveNext();
			}

			StockDataCache.stockBuySell.source=finalArray;

			sellArray=null;
			mfList=null
			matchArray=null;
			trace("MF : calculate buy sell  :\t " + new Date().toTimeString() + " \t:  Ended");
			//		for each (object in buyArray)
			//			trace(object.st_stockid + "\t"+object.st_quantity + "\t"+object.st_available);
		}

	}

}